package com.purengage.ipilih.lib;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBClass extends SQLiteOpenHelper {

	public DBClass(Context context) {
		super(context, "dbconfig", null, 26);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE if not exists config "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "key TEXT, "
				+ "value TEXT);");

		// mode 1:status, 2:inbox, 3:group
		db.execSQL("CREATE TABLE if not exists msg "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "id INT, "
				+ " message TEXT, date TEXT, user TEXT, mode INT, read INT);");

		db.execSQL("CREATE TABLE if not exists survey "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ " id INT,"
				+ " title TEXT, target_total INT, date_stop TEXT, date_modified TEXT," 
				+ " status TEXT, is_locked TEXT, description TEXT," 
				+ " target_qualification TEXT, points_per_data INT, options TEXT);");

		db.execSQL("CREATE TABLE if not exists komentar "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ "id INT, "
				+ "message TEXT, date_created TEXT, from_user_name TEXT, "
				+ "reply_to_status_id TEXT);");
		
		db.execSQL("CREATE TABLE if not exists answer "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "id INT, "
				+ "name TEXT, input TEXT, option TEXT);");
		
		db.execSQL("CREATE TABLE if not exists tmp "
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT, qid TEXT, "
				+ "name TEXT, answer TEXT);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS config");
		db.execSQL("DROP TABLE IF EXISTS msg");
		db.execSQL("DROP TABLE IF EXISTS survey");
		db.execSQL("DROP TABLE IF EXISTS komentar");
		db.execSQL("DROP TABLE IF EXISTS answer");
		db.execSQL("DROP TABLE IF EXISTS tmp");
		onCreate(db);
	}

	public void insertConf(Config c) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("key", c.getKey().toLowerCase().trim());
		values.put("value", c.getValue());

		db.insert("config", null, values);
		db.close();
	}

	public List<Config> getConf() {
		List<Config> list = new ArrayList<Config>();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select _id,key,value " + "From config", null);

		if (cur.moveToFirst()) {
			do {
				Config c = new Config();
				c.setKey(cur.getString(cur.getColumnIndex("key")));
				c.setValue(cur.getString(cur.getColumnIndex("value")));

				list.add(c);
			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return list;
	}

	public String getValue(String key) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select value from config " + "Where key='"
				+ key.toLowerCase() + "'", null);

		String val = "";
		if (cur.moveToFirst()) {
			do {
				val = cur.getString(cur.getColumnIndex("value"));

			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return val;
	}

	public boolean is_login() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select _id " + "From config", null);

		int i = 0;
		if (cur.moveToFirst()) {
			do {
				i++;
			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		if (i > 0)
			return true;
		else
			return false;

	}

	public void deleteConf() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From config";

		db.execSQL(str);
		db.close();
	}

	public void deleteSurvey() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From survey";

		db.execSQL(str);
		db.close();
	}

	public void insertMsg(Msg m) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("id", m.getId());
		values.put("message", m.getMessage().replace("%20", " "));
		values.put("user", m.getUser());
		values.put("date", m.getDate());
		values.put("mode", m.getMode());
		values.put("read", 0);

		db.insert("msg", null, values);
		db.close();
	}

	public Msg getMsgById(int id) {
		Msg m = new Msg();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("Select message, date, user "
				+ "From msg where id = " + id, null);
		if (cur != null)
			cur.moveToFirst();
		m.setMessage(cur.getString(cur.getColumnIndex("message")));
		m.setDate(cur.getString(cur.getColumnIndex("date")));
		m.setUser(cur.getString(cur.getColumnIndex("user")));
		cur.close();
		db.close();
		return m;

	}

	public List<Msg> getMsg(int mode) {
		List<Msg> list = new ArrayList<Msg>();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db
				.rawQuery("Select id, message, date, user "
						+ "From msg where mode = " + mode
						+ " order by date desc", null);

		if (cur.moveToFirst()) {
			do {
				Msg m = new Msg();
				m.setId(cur.getInt(cur.getColumnIndex("id")));
				m.setMessage(cur.getString(cur.getColumnIndex("message")));
				m.setDate(cur.getString(cur.getColumnIndex("date")));
				m.setUser(cur.getString(cur.getColumnIndex("user")));

				if(!cur.getString(cur.getColumnIndex("message")).equalsIgnoreCase(""))
					list.add(m);
				
			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return list;
	}

	public int getUnreadMsg() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db
				.rawQuery("Select id " + "From msg where read = 0", null);

		int i = 0;
		if (cur.moveToFirst()) {
			do {
				i++;
			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return i;
	}

	public String getDateMsg(int mode) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("select date from msg " + "where mode = "
				+ mode + " order by date desc limit 0,1", null);

		String date = "";
		if (cur.moveToFirst()) {
			do {
				date = cur.getString(cur.getColumnIndex("date"));

			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return date;
	}
	
	public String getDateLastKomen(int id){
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("select date_created from komentar " + "where reply_to_status_id = "
				+ id + " order by date_created desc limit 0,1", null);
		String date = "";
		if (cur.moveToFirst()) {
			do {
				date = cur.getString(cur.getColumnIndex("date_created"));

			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return date;
	}

	public void updateReadMsg(int mode) {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Update msg set read = 1 where mode=" + mode;

		db.execSQL(str);
		db.close();
	}

	public void insertSurvey(Surveys sv) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();

		values.put("id", sv.getId());
		values.put("title", sv.getTitle());
		values.put("target_total", sv.getTarget_total());
		values.put("target_qualification", sv.getTarget_qualification());
		values.put("points_per_data", sv.getPoints_per_data());
		values.put("date_stop", sv.getDate_stop());
		values.put("date_modified", sv.getDate_modified());
		values.put("status", sv.getStatus());
		values.put("is_locked", sv.getIs_locked());
		values.put("description", sv.getDescription());
		values.put("options", sv.getOptions());
		
		db.insert("survey", null, values);
		db.close();
	}

	public Surveys getSurveys(int is_lock) {
		Surveys s = new Surveys();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select id, title, target_total, date_stop, status "
						+ "From survey where is_locked = " + is_lock, null);

		if (cur != null)
			cur.moveToFirst();
		s.setId(cur.getInt(cur.getColumnIndex("id")));
		s.setTitle(cur.getString(cur.getColumnIndex("title")));
		s.setTarget_total(cur.getInt(cur.getColumnIndex("target_total")));
		s.setDate_stop(cur.getString(cur.getColumnIndex("date_stop")));
		s.setStatus(cur.getString(cur.getColumnIndex("status")));
		cur.close();
		db.close();
		return s;

	}

	public List<Surveys> getListSurveys() {
		List<Surveys> list = new ArrayList<Surveys>();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select id, title, target_total, date_stop, status, is_locked "
						+ "From survey", null);

		if (cur.moveToFirst()) {
			do {
				Surveys s = new Surveys();
				s.setId(cur.getInt(cur.getColumnIndex("id")));
				s.setTitle(cur.getString(cur.getColumnIndex("title")));
				s.setTarget_total(cur.getInt(cur.getColumnIndex("target_total")));
				s.setDate_stop(cur.getString(cur.getColumnIndex("date_stop")));
				s.setStatus(cur.getString(cur.getColumnIndex("status")));
				s.setIs_locked(cur.getString(cur.getColumnIndex("is_locked")));
				list.add(s);
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		return list;
	}

	public Surveys getSurveyById(int id) {
		Surveys s = new Surveys();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select title, target_total, date_stop, description, points_per_data, is_locked, "
						+ "target_qualification, options "
						+ "From survey where id = " + id, null);

		if (cur != null)
			cur.moveToFirst();
		s.setTitle(cur.getString(cur.getColumnIndex("title")));
		s.setTarget_total(cur.getInt(cur.getColumnIndex("target_total")));
		s.setTarget_qualification(cur.getString(cur.getColumnIndex("target_qualification")));
		s.setPoints_per_data(cur.getInt(cur.getColumnIndex("points_per_data")));
		s.setDate_stop(cur.getString(cur.getColumnIndex("date_stop")));
		s.setIs_locked(cur.getString(cur.getColumnIndex("is_locked")));
		s.setDescription(cur.getString(cur.getColumnIndex("description")));
		s.setOptions(cur.getString(cur.getColumnIndex("options")));
		
		cur.close();
		db.close();
		
		return s;

	}
	
	public String getDateSurvey() {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery("select date_modified from survey " 
				+ "order by date_modified desc limit 0,1", null);

		String date = "";
		if (cur.moveToFirst()) {
			do {
				date = cur.getString(cur.getColumnIndex("date_modified"));

			} while (cur.moveToNext());
		}

		cur.close();
		db.close();

		return date;
	}

	public void insertKomen(Komen k) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put("id", k.getId());
		values.put("message", k.getMessage().replace("%20", " "));
		values.put("date_created", k.getDate_created());
		values.put("from_user_name", k.getFrom_user_name());
		values.put("reply_to_status_id", k.getReply_to_status_id());
		db.insert("komentar", null, values);
		db.close();
	}

	public List<Komen> getListKomen(int id) {
		List<Komen> list = new ArrayList<Komen>();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select message, date_created, from_user_name "
						+ "From komentar where reply_to_status_id = " + id,
				null);

		if (cur.moveToFirst()) {
			do {
				Komen k = new Komen();
				k.setMessage(cur.getString(cur.getColumnIndex("message")));
				k.setDate_created(cur.getString(cur
						.getColumnIndex("date_created")));
				k.setFrom_user_name(cur.getString(cur
						.getColumnIndex("from_user_name")));
				list.add(k);
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		return list;
	}
	
	public void insertAnswer(Answer a){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		
		values.put("id", a.getId());
		values.put("name", a.getName());
		values.put("input", a.getInput());
		values.put("option", a.getOption());
		
		db.insert("answer", null, values);
		db.close();
	}
	
	public void deleteAnswer() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From answer";

		db.execSQL(str);
		db.close();
	}
	
	public Answer getAnswerById(int id){
		Answer a = new Answer();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select id, name, input, option "						
						+ "From answer where id = " + id, null);

		if (cur != null)
			cur.moveToFirst();
		
		a.setId(cur.getInt(cur.getColumnIndex("id")));
		a.setName(cur.getString(cur.getColumnIndex("name")));
		a.setInput(cur.getString(cur.getColumnIndex("input")));
		a.setOption(cur.getString(cur.getColumnIndex("option")));
		
		cur.close();
		db.close();
		
		return a;
	}
	
	public void insertTmp(Tmp t){
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
				
		//android.util.Log.i("--tmp--", t.getName()+" - "+t.getAnswer()+" - "+t.getQid());
		
		values.put("name", t.getName());
		values.put("qid", t.getQid());
		values.put("answer", t.getAnswer());		
		
		db.insert("tmp", null, values);
		db.close();
	}
	
	public void deleteTmp() {
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From tmp";

		db.execSQL(str);
		db.close();
	}
	
	public boolean checkTmpByQid(String qid){
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cur = db.rawQuery(
				"Select _id,name,answer "
						+ "From tmp where qid = '" + qid +"'",
				null);
		boolean sts = false;
		if (cur.moveToFirst()) {
			do {
				sts = true;
			} while (cur.moveToNext());
		}
		cur.close();
		db.close();
		
		return sts;
	}
	
	public void deteteTmpByQid(String qid){
		SQLiteDatabase db = this.getWritableDatabase();
		String str = "Delete From tmp Where qid = '"+ qid +"'";

		db.execSQL(str);
		db.close();
	}
	
	
}
